﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_ZiJin_G]
	(
		@CompanyId VARCHAR(500),
		@Today nchar(10)
	)
AS
BEGIN
	--DECLARE @strSql VARCHAR(4000)
	--SET @strSql='
	--;WITH list As(Select ROW_NUMBER() OVER (ORDER BY (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh)AS Row
	--,c.Id 
	--,c.CompanyName,c.Mid,dwbh
	--,ISNULL((Select IBA From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBA--实有现金
	--,ISNULL((Select IBB From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBB--实有银行
	--                                                                                                                            --
	--,ISNULL((Select Top 1 IBD From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBD--应有现金
	--,ISNULL((Select Top 1 IBE From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBE--应有银行
	--,ISNULL((Select IBC From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBC--账户余额	                                                                                                                                  
	--,ISNULL((Select Top 1 IBF From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBF--借款合计
	--,ISNULL((Select Top 1 IBG From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBG--资金合计                                                                                                                                  
	--,ISNULL((Select Sum(IBH) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBH--应有现金平整	                                                                                                                                  
	--,ISNULL((Select Sum(IBI) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBI--应有银行平整	                                                                                                                                  
	--,ISNULL((Select sum(ibj) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBJ--差额
	--,ISNULL((Select IBK From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBK--应收合计
	--,ISNULL((Select IBL From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IBL--应付合计	                                                                                                                                            	                                                                                                                                  --	                                                                                                                                                                                                                                                              
	--From Company c Where ('''+@CompanyId+'''=''0'' Or Id in ('+@CompanyId+')) 
	--)
	--Select * From list,(SELECT SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	--,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	--,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a	
	-- FROM list) AS n  Order by (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh'
	-- PRINT @strSql
	-- EXEC (@strSql)
	
	DECLARE @strSql VARCHAR(4000),@strSql1 varchar(4000),@strWhere varchar(1000)
	set @strWhere=''
	if @CompanyId <>''
	begin
		set @strWhere='where Id in ('+@CompanyId+')'
	end
	SET @strSql='
	
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, '''+@Today + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@Today + ' 23:59:59'')
		
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh)AS Row
	,c.Id 
	,c.CompanyName,isnull(c.Mid,'''') as Mid,dwbh
	,ISNULL((Select IBA From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBA--实有现金
	,ISNULL((Select IBB From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBB--实有银行
	                                                                                                                            --
	,ISNULL((Select Top 1 IBD From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBD--应有现金
	,ISNULL((Select Top 1 IBE From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBE--应有银行
	,ISNULL((Select top 1 IBC From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq order by id desc),0) As IBC--账户余额	                                                                                                                                  
	,ISNULL((Select Top 1 IBF From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBF--借款合计
	,ISNULL((Select Top 1 IBG From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBG--资金合计                                                                                                                                  
	,ISNULL((Select Sum(IBH) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBH--应有现金平整	                                                                                                                                  
	,ISNULL((Select Sum(IBI) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBI--应有银行平整	                                                                                                                                  
	,ISNULL((Select Sum(ibj) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBJ--差额
	,ISNULL((Select sum(IBK) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBK--应收合计
	,ISNULL((Select sum(IBL)  From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@sRq and CreateDate<=@eRq),0) As IBL--应付合计	                                                                                                                                            	                                                                                                                                  --	                                                                                                                                                                                                                                                              
	From Company c '+@strWhere+'
	)'
	set @strSql1=' 
	Select * From list,(SELECT SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a	
	 FROM list) AS n  Order by (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh'
	 
	 
	 
	 
	 
	-- PRINT @strSql+@strSql1
	 EXEC (@strSql+@strSql1)
End
